package org.hentai.bt;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.io.SAXReader;
import org.hentai.bt.util.sql.SqlConnection;
import org.hentai.bt.util.xml.XmlMap;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

/**
 * 通过迁移文件生成 sql 语句，并生成表
 */
public class Migrate {

    private File before;
    private File file;

    private int num = 0;

    public void migrate() throws SQLException, ClassNotFoundException, DocumentException, IOException {
        List<String> sql = getSql();

        System.out.println(sql);
        if (sql == null) {
            return;
        }

        for (String s : sql) {
            exec(s);
        }

        File migrationProperties = new File("migration.properties");

        // 标记生成
        if (!migrationProperties.exists()) {
            migrationProperties.createNewFile();
        }

        BufferedWriter bw = new BufferedWriter(new FileWriter(migrationProperties));
        bw.write("migration=" + (num - 1));
        bw.close();

    }

    /**
     * 执行
     *
     * @param sql
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private void exec(String sql) throws SQLException, ClassNotFoundException {
        Connection connection = SqlConnection.getConn(DbConnectionMsg.dbMsg);
        PreparedStatement ps = connection.prepareStatement(sql);
        boolean execute = ps.execute();
        ps.close();
        connection.close();
    }

    /**
     * 获得 sql 语句
     */
    private List<String> getSql() throws IOException, DocumentException {

        num = 0;
        before = null;
        file = new File(String.format("%04d.xml", num++));
        File after = new File(String.format("%04d.xml", num++));

        while (after.exists()) {
            before = file;
            file = after;
            after = new File(String.format("%04d.xml", num++));
        }

        File migrationProperties = new File("migration.properties");

        Properties properties = new Properties();
        String migration = null;
        if (migrationProperties.exists()) {
            properties.load(new FileInputStream(migrationProperties));
            migration = properties.getProperty("migration", "-1");
        }

        if (migration != null && migration.equals(Integer.toString(num - 1))) {
            System.out.println("表已经生成过了");
            return null;
        }

        if (before == null) {
            return firstMigrate();
        }

        return hasMigrated();
    }

    /**
     * 第一次迁移，生成创建数据
     */
    private List<String> firstMigrate() throws FileNotFoundException, DocumentException {

        SAXReader reader = new SAXReader();
        Document doc = reader.read(new FileInputStream(file));
        Map<String, List<String>> docTables = XmlMap.analyseXmlDocument(doc);

        List<String> sqls = new ArrayList<>();

        for (String tableName : docTables.keySet()) {
            List<String> tableFields = docTables.get(tableName);
            StringBuilder sql = new StringBuilder("CREATE TABLE ");
            sql.append(tableName).append("(");

            tableFields.removeIf(str -> str.trim().equals(""));

            for (int i = 0; i < tableFields.size(); i++) {
                sql.append(tableFields.get(i));
                if (i < tableFields.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(");");
            sqls.add(sql.toString());

        }

        sqls.sort(new Comparator<String>() {
            @Override
            public int compare(String o1, String o2) {

                if (o1.contains("FOREIGN KEY")) {
                    return 1;
                } else if (o2.contains("FOREIGN KEY")) {
                    return -1;
                }

                return 0;
            }
        });

        System.out.println(sqls);

        return sqls;
    }

    /**
     * 二次修改，比较差异生成sql
     */
    private List<String> hasMigrated() throws IOException, DocumentException {

        SAXReader reader = new SAXReader();

        Document beforeDoc = reader.read(new FileInputStream(before));
        Map<String, List<String>> beforeFields = XmlMap.analyseXmlDocument(beforeDoc);
        Document doc = reader.read(new FileInputStream(file));
        Map<String, List<String>> docFields = XmlMap.analyseXmlDocument(doc);

        // 原先表
        Set<String> beforeKeys = beforeFields.keySet();
        // 修改后表
        Set<String> keys = docFields.keySet();

        List<String> sqlS = new ArrayList<>();
        StringBuilder alterSql = new StringBuilder();

        // 冲突 alter
        for (String beforeKey : beforeKeys) {
            List<String> bFields = null;
            List<String> fields = null;
            if (keys.contains(beforeKey)) {
                bFields = beforeFields.get(beforeKey);
                fields = docFields.get(beforeKey);
                beforeKeys.remove(beforeKey);
                keys.remove(beforeKey);
            }

            if (bFields == null || fields == null) {
                continue;
            }

            bFields.removeIf(next -> next.equals(""));
            fields.removeIf(next -> next.equals(""));

            String equalsFields = equalsFields(bFields, fields);
            sqlS.add(
                    "ALTER TABLE " + beforeKey + " " + equalsFields + ";\n\n"
            );
            alterSql.append("ALTER TABLE ").append(beforeKey).append(" ").append(equalsFields).append(";\n\n");
        }

        // 表 移除 drop
        for (String beforeKey : beforeKeys) {
            sqlS.add(
                    "DROP TABLE " + beforeKey + ";\n\n"
            );
            alterSql.append("DROP TABLE ").append(beforeKey).append(";\n\n");
        }

//        // 表 新增 create
//        for (String key : keys) {
//            sqlS.add(
//                    new StringBuilder().append("CREATE TABLE ").append(key).append("(").toString()
//            );
//            alterSql.append("CREATE TABLE ").append(key).append("(");
//            List<String> newTableFields = docFields.get(key);
//
//            newTableFields.removeIf(next -> next.equals(""));
//
//            for (int i = 0; i < newTableFields.size(); i++) {
//
//            }
//            System.out.print("create table ");
//            System.out.println(key);
//        }

        return sqlS;
    }

    // 冲突对比
    private String equalsFields(List<String> before, List<String> now) {

        Iterator<String> bIterator = before.iterator();

        while (bIterator.hasNext()) {
            String next = bIterator.next();
            if (now.contains(next)) {
                bIterator.remove();
                now.remove(next);
            }
        }

        StringBuilder stringBuilder = new StringBuilder();

        // 全新增字段
        if (before.isEmpty() && !now.isEmpty()) {
            for (int i = 0; i < now.size(); i++) {
                String fieldName = now.get(i).trim().split("\\s+")[0];
                stringBuilder.append("ADD COLUMN ").append(now.get(i).trim());
                if (i < now.size() - 1) {
                    stringBuilder.append(",");
                }
            }
        }

        // 全移除字段
        if (!before.isEmpty() && now.isEmpty()) {
            for (int i = 0; i < before.size(); i++) {
                String fieldName = before.get(i).trim().split("\\s+")[0];
                stringBuilder.append("DROP COLUMN ").append(fieldName);
                if (i < before.size() - 1) {
                    stringBuilder.append(",");
                }
            }
        }

        // 冲突
        if (!before.isEmpty() && !now.isEmpty()) {
            // TODO: 2022/5/21 ALTER

        }


        return stringBuilder.toString();
    }


    /**
     * 判断字段是否修改
     */
    public static boolean xmlHasChanged(File file, Document document) throws FileNotFoundException, DocumentException {
        Map<String, List<String>> xmlDocuments = XmlMap.analyseXmlDocument(document);
        SAXReader reader = new SAXReader();
        Document doc = reader.read(new FileInputStream(file));
        Map<String, List<String>> beforeXmlDocument = XmlMap.analyseXmlDocument(doc);
        return !beforeXmlDocument.equals(xmlDocuments);
    }

}
